package com.cym.utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.hutool.core.util.StrUtil;

public class SqlChanger {

	public static String change(String oldSql, String view, String schemaName) {

		String oldSchema = getOldSchema(oldSql);
		String table = getTable(oldSql);

		List<String> list = new ArrayList<>();
		List<String> strings = processCphFields(oldSql, view, schemaName);
		list.add("\\c ods;");
		oldSql = oldSql.replace("\\n", "\n").replace(" --", " , --").replace("NOT NULL", ", -- NOT NULL");
		oldSql = processSql(oldSql);

	
		String sqlOds = oldSql.replace(oldSchema, "ods_" + schemaName);
		sqlOds = StrUtil.replaceIgnoreCase(sqlOds, table, "ods_" + view); //替换表名
		list.add(sqlOds);
		list.add("CREATE TABLE IF NOT EXISTS ods_" + schemaName + ".ods_" + view + "_tmp (LIKE ods_" + schemaName + "." + "ods_" + view + ");");

		list.addAll(strings);

		list.add("");
		list.add("");
		list.add("\\c stg;");
		String sqlStg = oldSql.replace(oldSchema, "stg_" + schemaName);
		sqlStg = StrUtil.replaceIgnoreCase(sqlStg, table, "stg_" + view); //替换表名
		list.add(sqlStg);
		list.add("CREATE TABLE IF NOT EXISTS stg_" + schemaName + ".stg_" + view + "_tmp1 (LIKE stg_" + schemaName + "." + "stg_" + view + ");");
		list.add("CREATE TABLE IF NOT EXISTS stg_" + schemaName + ".stg_" + view + "_tmp2 (LIKE stg_" + schemaName + "." + "stg_" + view + ");");

		return StrUtil.join("\n", list);
	}

	private static String getTable(String oldSql) {
		String[] strs = oldSql.split("\n");

		for (String str : strs) {

			if (str.trim().toUpperCase().startsWith("CREATE TABLE")) {
				String oldSchema = str.split("\\s+")[2].split("\\.")[1].replace("\"", "");
				return oldSchema;
			}
		}

		return null;
	}

	private static String getOldSchema(String oldSql) {
		String[] strs = oldSql.split("\n");

		for (String str : strs) {

			if (str.trim().toUpperCase().startsWith("CREATE TABLE")) {
				String oldSchema = str.split("\\s+")[2].split("\\.")[0];
				return oldSchema.replace("\"", "");
			}
		}

		return null;
	}

	public static void main(String[] args) {
		System.out.println(getTable(sql));
	}

	private static String processSql(String sql) {
		// 按行分割 SQL 字符串
		String[] lines = sql.split("\n");
		List<String> results = new ArrayList<String>();

		for (int i = 0; i < lines.length; i++) {
			String line = lines[i].trim();
			// 检查是否是 CONSTRAINT 行
			if (line.contains("CPH_")) {
				// 跳过这一行
				continue;
			}
			if (!line.contains("CREATE TABLE")){
				line = line.replaceAll("\"", "");
				line = line.toLowerCase();
			}
			// 检查是否是 CONSTRAINT 行
			if (StrUtil.containsIgnoreCase(line, "CONSTRAINT")) {
				if (!results.contains("col_date_lt DATE DEFAULT CURRENT_DATE")) {
					results.add("col_date_lt DATE DEFAULT CURRENT_DATE");
				}
				continue;
			}

//			// 检查前一行是否是字段定义（以逗号结尾）
//			if (i > 0 && lines[i - 1].trim().endsWith(",") && (i + 1 < lines.length && lines[i + 1].trim().startsWith("CONSTRAINT"))) {
//				// 去掉前一行末尾的逗号
//				String prevLine = lines[i - 1].trim();
//				if (prevLine.endsWith(",")) {
//					prevLine = prevLine.substring(0, prevLine.length() - 1);
//					results.add(prevLine);
//				}
//			}

			// 添加当前行到结果中（如果不是 CONSTRAINT 行）
			results.add(line);

		}

		return StrUtil.join("\n", results);
	}

	public static String sql = "-- 表: sjhj_mzt.WT_2029638145 (企业认证信息)\n"
			+ "CREATE TABLE sjhj_mzt.\"WT_2029638145\" (\\n  id bigint NOT NULL DEFAULT nextval('company_condition_id_seq'::regclass) -- 主键id,\n" + "  company_id bigint NOT NULL -- 公司id,\n"
			+ "  name character varying(60) -- 企业名称,\n" + "  company_type_ids character varying(255) -- 企业类型id,\n" + "  corporation character varying(60) -- 法人,\n"
			+ "  contact_name character varying(60) -- 联系人姓名,\n" + "  contact_phone character varying(12) -- 联系人手机号,\n" + "  region_code integer -- 所在地区行政区代码,\n"
			+ "  address character varying(60) -- 详细地址,\n" + "  email character varying(255) -- 电子邮件,\n" + "  web character varying(100) -- 企业官网地址,\n"
			+ "  logo character varying(255) -- 企业logo的附件地址,\n" + "  organization_code character varying(50) -- 组织机构代码,\n" + "  register_region_code integer -- 注册地址行政区代码,\n"
			+ "  register_address character varying(60) -- 注册详细地址,\n" + "  register_money numeric(10,2) -- 注册资本，万元,\n" + "  born_time date -- 成立时间,\n" + "  company_status integer -- 企业状态,\n"
			+ "  registration_authority character varying(60) -- 登记机关,\n" + "  business_scope text -- 经营范围,\n" + "  postal_code character varying(8) -- 邮政编码,\n"
			+ "  fax_code character varying(13) -- 传真号码,\n" + "  business_license character varying(600) -- 营业执照地址，存储json,list,\n" + "  count_ipr integer -- 知识产个数,\n"
			+ "  company_credit character varying(255) -- 企业/机构信用,\n" + "  annex_ids text -- 其他附件id,使用,分割转换成对象,\n" + "  deleted boolean NOT NULL DEFAULT false -- 删除标识;0:未删除 1:删除,\n"
			+ "  create_user_id bigint NOT NULL -- 创建用户id,\n" + "  create_time timestamp without time zone NOT NULL -- 创建时间,\n" + "  update_user_id bigint -- 更新用户id,\n"
			+ "  update_time timestamp without time zone -- 更新时间,\n" + "  \"CPH_contact_phone_\" character varying(133),\n" + "  CONSTRAINT company_condition_pkey PRIMARY KEY (id)\n" + ");\n"
			+ "COMMENT ON TABLE sjhj_mzt.\"WT_2029638145\" IS '企业认证信息';\\nCOMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".id IS '主键id';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".company_id IS '公司id';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".name IS '企业名称';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".company_type_ids IS '企业类型id';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".corporation IS '法人';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".contact_name IS '联系人姓名';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".contact_phone IS '联系人手机号';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".region_code IS '所在地区行政区代码';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".address IS '详细地址';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".email IS '电子邮件';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".web IS '企业官网地址';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".logo IS '企业logo的附件地址';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".organization_code IS '组织机构代码';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".register_region_code IS '注册地址行政区代码';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".register_address IS '注册详细地址';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".register_money IS '注册资本，万元';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".born_time IS '成立时间';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".company_status IS '企业状态';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".registration_authority IS '登记机关';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".business_scope IS '经营范围';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".postal_code IS '邮政编码';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".fax_code IS '传真号码';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".business_license IS '营业执照地址，存储json,list';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".count_ipr IS '知识产个数';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".company_credit IS '企业/机构信用';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".annex_ids IS '其他附件id,使用,分割转换成对象';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".deleted IS '删除标识;0:未删除 1:删除';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".create_user_id IS '创建用户id';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".create_time IS '创建时间';\n"
			+ "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".update_user_id IS '更新用户id';\n" + "COMMENT ON COLUMN sjhj_mzt.\"WT_2029638145\".update_time IS '更新时间';";

	private static Map<String, String> parseFieldInfo(String sql) {
		Map<String, String> fieldInfo = new HashMap<>();

		// 正则表达式匹配字段定义
		Pattern pattern = Pattern.compile("\"([^\"]+)\"\\s+VARCHAR\\s*\\(\\s*(\\d+)\\s*\\)");
		Matcher matcher = pattern.matcher(sql);

		while (matcher.find()) {
			String fieldName = matcher.group(1);
			String length = matcher.group(2);
			fieldInfo.put(fieldName, length);
		}

		return fieldInfo;
	}

	// 处理 CPH_ 前缀字段
	private static List<String> processCphFields(String sql, String table, String schema) {
		table = "ods_" + table;
		List<String> list = new ArrayList<>();
		// 解析字段信息
//		Map<String, String> fieldInfo = parseFieldInfo(sql);
		Map<String, String> fieldInfo = processSqlFields(sql);

		for (String fieldName : fieldInfo.keySet()) {
			if (fieldName.contains("CPH_")) {
				// 去掉 CPH_ 前缀


				} else {
					System.out.println("未找到与 '" + fieldName + "' 对应的原始字段");
				}
			String baseName = fieldName.substring(4);
			baseName = baseName.substring(0, baseName.length() - 1);
			// 查找对应的原始字段
			// 打印原始字段长度
			String length = fieldInfo.get(fieldName);
			String newLength = null;
			if ("text".equals(length)){

			}else {
				Integer newLength2 = Integer.valueOf(length);
				newLength = String.valueOf(newLength2 * 10);
			}

//                    System.out.println("原始字段 '" + baseName + "' 的长度为: " + length);
			// 构造新字段名 (添加 _encode 后缀)
			String newFieldName = baseName + "_encode";
//                    System.out.println("应修改字段名为: " + newFieldName);

			String alert = "ALTER TABLE ods_" + schema + "." + table + " RENAME COLUMN " + baseName + " TO " + newFieldName + ";";
			String alert2 = "ALTER TABLE ods_" + schema + "." + table + " ALTER COLUMN " + newFieldName + " TYPE VARCHAR(%s);";
			String alert3 = "ALTER TABLE ods_" + schema + "." + table + " ALTER COLUMN " + newFieldName + " TYPE text;";
			String format = String.format(alert2, newLength);
//					String replace = alert2.replace("50", newLength.toString());
			System.out.println(alert);
			System.out.println(format);
			list.add(alert);
			if ("text".equals(length)){
				list.add(alert3);
			}else {
				list.add(format);
			}
		}
		return list;
	}


	private static Map<String, String> processSqlFields(String sql) {
		Map<String, String> fieldInfo = new HashMap<>();
		// 正则表达式匹配字段定义
		Pattern pattern = Pattern.compile("(?:\"([^\"]+)\"|(\\w+))\\s+(\\w+)(?:\\s+varying\\s*\\((\\d+)\\))?");
		Matcher matcher = pattern.matcher(sql);

		while (matcher.find()) {
			String fieldName = matcher.group(1) != null ? matcher.group(1) : matcher.group(2);
			String dataType = matcher.group(3);
			String length = matcher.group(4) != null ? matcher.group(4) : "text"; // 提取长度，如果没有则为 N/A


			// 跳过 COMMENT 部分
			if (fieldName.equalsIgnoreCase("COMMENT")) {
				continue;
			}
			if (!fieldName.contains("CPH_")){
				continue;
			}

			// 处理带引号的字段名
			if (fieldName.startsWith("\"") && fieldName.endsWith("\"")) {
				fieldName = fieldName.substring(1, fieldName.length() - 1);
			}

			// 打印字段名和长度
			if (!length.isEmpty()) {
				System.out.println("字段名: " + fieldName + ", 长度: " + length);
				fieldInfo.put(fieldName, length);
			}
		}
		return fieldInfo;
	}

	public static String other(String oldSql, String view, String schemaName) {
		List<String> cols = new ArrayList<String>();

		String[] sqls = oldSql.split("\n");
		boolean start = false;
		for (String sql : sqls) {
			if (sql.trim().toUpperCase().startsWith("CREATE TABLE")) {
				start = true;
				continue;
			}
			if(sql.trim().toUpperCase().contains("CPH_")) {
				continue;
			}
			
			if (sql.trim().toUpperCase().startsWith(")")) {
				break;
			}

			if (start && !sql.trim().toUpperCase().startsWith("CONSTRAINT") && !sql.trim().toUpperCase().startsWith("CPH_")) {
				cols.add(sql.trim().split("\\s+")[0]);
			}
		}

		List<String> selectSqls = new ArrayList<String>();
		selectSqls.add("select " + StrUtil.join(",", cols) + " from " + getOldSchema(oldSql) + "." + view);

		selectSqls.add("TRUNCATE TABLE ods_" + schemaName + ".ods_" + view + "_tmp;");

		selectSqls.add("TRUNCATE TABLE stg_" + schemaName + ".stg_" + view + "_tmp1;");

		selectSqls.add("TRUNCATE TABLE stg_" + schemaName + ".stg_" + view + "_tmp2;");

		return StrUtil.join("\n\n", selectSqls);
	}
}
